﻿Imports System.Data.SqlClient
Public Class clsNotaIngresoSalida
  Private cnDB As SqlConnection

  Public Sub New(ByVal psConnectString As String)
    cnDB = New SqlConnection(psConnectString)
  End Sub

  Public Function NotaIngresoSalidaListar(ByVal psiTipDoc As Int16, Optional ByRef psdFecIni As DateTime = Nothing, Optional ByRef psdFecFin As DateTime = Nothing) As DataTable
    Dim sqlCmd As New SqlClient.SqlCommand
    Dim oSqlda As SqlDataAdapter
    Dim oDataTable As New DataTable
    Try
      sqlCmd.Connection = cnDB
      sqlCmd.CommandType = CommandType.StoredProcedure
      sqlCmd.CommandText = "spNotaIngresoSalidaListar"
      sqlCmd.Parameters.Add("@psiTipDoc", SqlDbType.SmallInt).Value = psiTipDoc
      sqlCmd.Parameters.Add("@psdFecIni", SqlDbType.SmallDateTime).Value = IIf(psdFecIni = Nothing, DBNull.Value, psdFecIni)
      sqlCmd.Parameters.Add("@psdFecFin", SqlDbType.SmallDateTime).Value = IIf(psdFecFin = Nothing, DBNull.Value, psdFecFin)
      cnDB.Open()

      oSqlda = New SqlDataAdapter(sqlCmd)
      oSqlda.Fill(oDataTable)
      oSqlda.Dispose()
      sqlCmd.Dispose()

      Return oDataTable

    Catch ex As Exception
      Throw
    Finally
      If cnDB.State = ConnectionState.Open Then cnDB.Close()
    End Try
  End Function

  Public Function NotaDeIngresoInsertar(ByVal piNumNota As Int32, ByVal pcNumDoc As String, ByVal psiCodEmp As Int16, ByVal psiCodAlmOri As Int16, _
                                        ByVal psiCodAlmDes As Int16, ByVal psdFecDoc As DateTime, ByVal piNumOrdCom As Int32) As String
    Dim sqlcmd As New SqlCommand
    Try
      cnDB.Open()

      sqlcmd.Connection = cnDB
      sqlcmd.CommandType = CommandType.StoredProcedure
      sqlcmd.CommandText = "spNotaDeIngresoInsertar"

      sqlcmd.Parameters.Add("@piNumNota", SqlDbType.Int)
      sqlcmd.Parameters("@piNumNota").Direction = ParameterDirection.Output
      sqlcmd.Parameters.Add("@pcNumDoc", SqlDbType.Char, 14)
      sqlcmd.Parameters("@pcNumDoc").Direction = ParameterDirection.Output
      sqlcmd.Parameters.Add("@psiCodEmp", SqlDbType.SmallInt).Value = psiCodEmp
      sqlcmd.Parameters.Add("@psiCodAlmOri", SqlDbType.SmallInt).Value = psiCodAlmOri
      sqlcmd.Parameters.Add("@psiCodAlmDes", SqlDbType.SmallInt).Value = psiCodAlmDes
      sqlcmd.Parameters.Add("@psdFecDoc", SqlDbType.SmallDateTime).Value = psdFecDoc
      sqlcmd.Parameters.Add("@piNumOrdCom", SqlDbType.Int).Value = piNumOrdCom
      sqlcmd.ExecuteNonQuery()

      piNumNota = sqlcmd.Parameters("@piNumNota").Value
      pcNumDoc = sqlcmd.Parameters("@pcNumDoc").Value

      Return pcNumDoc

    Catch ex As Exception
      Throw New Exception(ex.Message)
    Finally
      If cnDB.State = ConnectionState.Open Then cnDB.Close()
      GC.Collect()
    End Try
  End Function

  Public Sub NotaDeIngresoActualizar(ByVal piNumNota As Int32, ByVal psiCodEmp As Int16)
    Dim sqlcmd As New SqlCommand
    Try
      cnDB.Open()

      sqlcmd.Connection = cnDB
      sqlcmd.CommandType = CommandType.StoredProcedure
      sqlcmd.CommandText = "spNotaDeIngresoActualizar"

      sqlcmd.Parameters.Add("@piNumNota", SqlDbType.Int).Value = piNumNota
      sqlcmd.Parameters.Add("@psiCodEmp", SqlDbType.SmallInt).Value = psiCodEmp

      sqlcmd.ExecuteNonQuery()


    Catch ex As Exception
      Throw New Exception(ex.Message)
    Finally
      If cnDB.State = ConnectionState.Open Then cnDB.Close()
      GC.Collect()
    End Try
  End Sub
End Class
